package user;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ExportOrclFun {
	OrcaleDataBaseHelper orcl = new OrcaleDataBaseHelper();

	String sql = null;

	ArrayList<String> names = new ArrayList<String>();

	ArrayList<String> txts = new ArrayList<String>();

	ResultSet rs = null;

	public void ALL_SOURCE(String TYPE) throws SQLException {

		sql = "SELECT name FROM ALL_SOURCE where TYPE='PROCEDURE' and owner='JYJ4' group by name";
		orcl.initialize("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "jyj4",
						"jyj4");

		rs = orcl.executeQuery(sql);
		while (rs.next()) {
			names.add(rs.getString(1));
		}
		orcl.close();
		for (int i = 0; i < names.size(); i++) {
			txts.add("");txts.add("");txts.add("");txts.add("");
			txts.add("/*"+TYPE+"		"+names.get(i)+"*/");
			sql = "SELECT text FROM ALL_SOURCE where TYPE='PROCEDURE' and owner='JYJ4' and name='"
					+ names.get(i) + "' order by line";
			rs = orcl.executeQuery(sql);
			txts.add("create or replace ");
			while (rs.next()) {
				txts.add(rs.getString(1));
			}
			orcl.close();
		}
	}

	public ExportOrclFun() {
		try {
			ALL_SOURCE("PROCEDURE");
			ALL_SOURCE("FUNCTION");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			orcl.close();
		}
		for (int i = 0; i < txts.size(); i++) {
			System.out.println(txts.get(i));
		}
	}
}
